EROC: A Toolkit for Building NEATO Query Optimizers

EROC (Extensible, Reusable Optimization Components) is a toolkit for building query optimizers. EROC’s components are C++ classes based on abstractions we have identified as central to query optimization, not only in relational DBMSs, but in extended relational and object-oriented DBMSs as well. I EROC’s use of C++ classes clarifies the mapping from application domain (optimization) abstractions to solution domain (EROC) abstractions, and these classes provide: (1) complex predicate definition and manipulation; (2) representations for common operators, such as join and groupby, and associated property derivation functions, including key derivation; (3) management of catalog and type information; (4) implementations of common algebraic equivalence rules, and (5) System Rand Volcano-style search strategies. The classes are designed to provide optimizer implementors reusability and extensibility through layering and inheritance. EROC provides much more functionality than previous optimization tools because at1 of Pemnission to copy without fee all 01 part of this material is granted provided thaf the copies are not made OT distributed fOT’ direct commercial advantage, the VLDB copyright notice and the title of the publication and ifs date appear, and notice is given that copying is by permission of the Ve’ery Large Data Base Endowment. To copy otherwise, OT to republish, sequires a fee .andbor special permission from fhe Endowment. Proceedings of the 22nd VLDB Conference Mumbai(Bombay), India, 1996 Melissa Truong NCR Corporation [email protected] EROC’s optimization classes are extensible and reusable, not just the search components. In addition to describing EROC’s architecture and software engineering, we also show how EROC’s classes were extended to build NEAT0 (New EROC-based Advanced Teradata Optimizer), a join optimizer for a massively parallel environment. Based on the extensions required we give an indication of the savings EROC, provided us. To show NEATO’s efficiency and effectiveness, we present results of optimizing complex TPC/D benchmark queries and show that NEAT0 easily searches the entire space of query execution plans. We outline plans for extensions to NEAT0 and overview how the flexibility of EROC will enable these extensions.